﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_Business;

namespace ExtBusiness.FlowOperation
{
    public class HandleCaseService
    {
        /// <summary>
        /// 根据userid和期限查找顺序号集合
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="dtTemp"></param>
        public void GetIidByOverTimeAndUserId(string userId, out DataTable dtTemp) 
        {
            string strSql = "select 顺序号 from sv_work_list_for_worklist_node where 结点过期<0 and userid='" + userId + "' ";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
        }
        
        /// <summary>
        /// 得到挂起案例集合
        /// </summary>
        /// <param name="strXmmc"></param>
        /// <param name="strDis"></param>
        /// <param name="strserialapplyer"></param>
        /// <param name="strapplyer"></param>
        /// <param name="d_s"></param>
        /// <param name="d_e"></param>
        /// <param name="strIID"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable GetHangCaseList(string strXmmc,string strDis,string strserialapplyer,string strapplyer,string d_s,string d_e,string strIID,string userId)
        {
            string sql = string.Empty;
            DataTable rs1;
            sql = @"select a.serial,a.handleid,a.suspend_type,a.userid,apply_time,a.memo,a.id,
                                          (select start_date from st_suspend where 1 = 2) start_date
                                          from xt_suspend_apply a where 1 = 1 and a.userid = '{0}' {1} {2} {3} and a.checkuserid is null
                                          union all 
                                          select a.serial,a.handleid,a.suspend_type,a.userid,apply_time,a.memo,a.id,b.start_date
                                          from xt_suspend_apply a, st_suspend b where a.id = b.xt_suspend_id and a.userid = '{0}' {1} {2} {3} 
                                          and b.end_userid is null";
            string startData = "";
            string endData = "";
            string strWhereIID = "";
            if ((!String.IsNullOrEmpty(d_s)) && (!String.IsNullOrEmpty(d_e)))
            {
                startData = string.Format(" and a.apply_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')", d_s);
                endData = string.Format(" and a.apply_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')", d_e);

            }
            if (!string.IsNullOrEmpty(strIID))
            {
                strWhereIID = string.Format(" and a.serial like '%{0}%'", strIID);
            }
            sql = string.Format(sql, userId, startData, endData, strWhereIID);
            SysParams.OAConnection().RunSql(sql, out rs1);
            return rs1;
        }

        /// <summary>
        /// 添加业务删除标识条件 and i.isdelete <>1
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetWnameByCondi(string id)
        {
            string strSqlWname = string.Format("select wname from st_workflow where wid=(select wid from  st_instance where iid='{0}' and isdelete <>1)", id);
            return SysParams.OAConnection().GetValue(strSqlWname);
        }
        /// <summary>
        ///根据当前登录用户ID查询出serial_id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetWidByCondi(string id)
        {
            string strSerialid = string.Format("select f.wid from  st_instance f where f.iid='" + id + "' and isdelete <>1");
            return SysParams.OAConnection().GetValue(strSerialid);
        }
        /// <summary>
        /// 根据当前登录用户ID查询出FLOW_NO
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wname"></param>
        /// <returns></returns>
        public string GetFlow_NoByUserIdAndWname(string userId, string wname)
        {
            string strUserNo = string.Format("select distinct a.FLOW_NO from XT_SUSPEND_FLOW a,XT_SUSPENDFLOW_USERID b where a.ID=b.FLOW_ID and b.USERID='{0}' and b.serial_id in( select wid from st_workflow where wname='{1}')", userId, wname);
            return SysParams.OAConnection().GetValue(strUserNo);
        }
        /// <summary>
        /// 根据id和s_gwId取得当前的记录
        /// </summary>
        /// <param name="id">iid</param>
        /// <param name="s_gwId">wiid</param>
        /// <returns></returns>
        public DataTable GetCurrentListByIdAndHandleId(string id, string s_gwId)
        {
            DataTable rs;
            string sql = "select suspend_type,memo,id from xt_suspend_apply where serial='" + id + "' and handleid='" + s_gwId + "' and ischeckup is null and isdelete is null";
            SysParams.OAConnection().RunSql(sql, out rs);
            return rs;
        }

        /// <summary>
        /// 挂起信息操作
        /// </summary>
        /// <param name="id"></param>
        /// <param name="Wname"></param>
        /// <param name="s_id"></param>
        /// <param name="s_type"></param>
        /// <param name="nextUserStepNo"></param>
        public void OperatSuspend_Add(String id, string Wname, String s_id, String s_type, int nextUserStepNo)
        {
            string messageText = string.Empty;
            string strSql = string.Empty;
            string nexeNo = string.Empty;
            DataTable dtMessage;

            switch (s_type)
            {
                case "gqsq"://挂起申请
                    //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                    messageText = "您在政务平台中有新的挂起申请业务待审批，请尽快处理！业务编号：" + id;
                    strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = '{0}' and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nextUserStepNo, Wname);

                    SysParams.OAConnection().RunSql(strSql, out dtMessage);
                    foreach (DataRow rowMessage in dtMessage.Rows)
                    {
                        //添加用户ID addby zhongjian 20091029
                        strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                                 messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                        SysParams.OAConnection().RunSql(strSql);
                    }
                    break;

                case "qxsq"://取消挂起申请
                    //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                    messageText = "业务编号为：" + id + " 的挂起申请业务已经取消申请！";
                    nexeNo = string.Format("(select nextno from xt_suspend_apply where id='{0}')", s_id);
                    strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = {0} and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nexeNo, Wname);

                    SysParams.OAConnection().RunSql(strSql, out dtMessage);
                    foreach (DataRow rowMessage in dtMessage.Rows)
                    {
                        //添加用户ID addby zhongjian 20091029
                        strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                                 messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                        SysParams.OAConnection().RunSql(strSql);
                    }
                    break;

                case "sqjg"://申请解挂
                    //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                    messageText = "业务编号为：" + id + " 的挂起业务现申请解挂，请尽快处理！";
                    nexeNo = string.Format("(select nextno from xt_suspend_apply where id='{0}')", s_id);
                    strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = {0} and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nexeNo, Wname);

                    SysParams.OAConnection().RunSql(strSql, out dtMessage);
                    foreach (DataRow rowMessage in dtMessage.Rows)
                    {

                        strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                                 messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                        SysParams.OAConnection().RunSql(strSql);
                    }
                    break;

                case "jcgq"://解除挂起
                    messageText = "业务编号为：" + id + " 的案件现已成功解挂，请查看！";
                    strSql = string.Format(@"select t.login_name,t.user_name,t.mobile from st_user t where t.userid = (select a.userid from xt_suspend_apply a where a.id='{0}')", s_id);
                    DataTable dtTemp = new DataTable();
                    SysParams.OAConnection().RunSql(strSql, out dtTemp);
                    if (dtTemp.Rows.Count > 0)
                    {
                        strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                               messageText, dtTemp.Rows[0]["mobile"].ToString(), dtTemp.Rows[0]["user_name"].ToString(), dtTemp.Rows[0]["login_name"].ToString());
                        SysParams.OAConnection().RunSql(strSql);
                    }
                    break;
            }
        }

        /// <summary>
        /// 构建挂起操作sql
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wName"></param>
        /// <param name="s_userid">用户id</param>
        /// <param name="id">案件编号iid</param>
        /// <param name="s_gwId">岗位id(wiid)</param>
        /// <param name="s_memo"></param>
        /// <param name="Wname">流程名称</param>
        /// <param name="s_suspentype"></param>
        /// <param name="s_id">xt_suspend_apply表的id</param>
        /// <param name="s_gwname">岗位名称</param>
        /// <param name="s_type"></param>
        /// <param name="jsHtml"></param>
        /// <param name="nextUserStepNo"></param>
        /// <returns></returns>
        public void HandleOperate(string userId,string wName, String s_userid, ref String strIID, ref String s_gwId, ref String s_memo, string Wname, ref String s_suspentype, String s_id, ref String s_gwname, String s_type, ref String jsHtml, ref int nextUserStepNo)
        {
            string sql = "";
            DataTable rs=null;
            if (s_type.Equals("gqsq"))//挂起申请
            {
                //根据当前登录用户ID查询出serial_id
                string s_serialid = GetWidByCondi(strIID);

                //挂起申请
                sql = string.Format("insert into xt_suspend_apply(serial,handleid,suspend_type,userid,apply_time,memo) values('{0}','{1}','{2}','{3}',sysdate,'{4}')", strIID, s_gwId, s_suspentype, s_userid, s_memo);
                SysParams.OAConnection().RunSql(sql);

                //针对地图审核业务直接挂起
                sql = string.Format("insert into st_suspend(iid,start_date,userid,description,stpname,xt_suspend_id,suspend_type) values('{0}',sysdate ,'{1}','{2}','{3}','{4}','{5}')", strIID, s_userid, s_memo, s_gwname, s_id, s_suspentype);
            }
            else if (s_type.Equals("qxsq") || s_type.Equals("06"))//取消挂起申请
            {
                //针对地图审核业务直接解挂
                sql = string.Format("update st_suspend set end_date=sysdate ,end_userid='{0}' where iid='{1}' ", s_userid, strIID);
                SysParams.OAConnection().RunSql(sql);

                //设置申请标志
sql = string.Format(" update xt_suspend_apply set isdelete='1',checkuserid='{0}',delete_time=sysdate where id='{1}'",s_userid,s_id);
            }
            else if (s_type.Equals("sqjg"))//申请解挂
            {
                //不做任何操作 只需要发送短信即可		
                sql = "select * from st_suspend where 1=2";
            }
            else if (s_type.Equals("pzgq"))//批准挂起
            {
                sql = string.Format("update xt_suspend_apply set checkuserid='{0}',check_time=sysdate,ischeckup='1' where id='{1}'",s_userid,s_id);
                SysParams.OAConnection().RunSql(sql);

                sql = string.Format("select serial,handleid,suspend_type,memo from xt_suspend_apply where id='{0}'",s_id);
                SysParams.OAConnection().RunSql(sql, out rs);

                //获得预申请信息，插入挂起表
                if (rs.Rows.Count > 0)
                {
                    strIID = rs.Rows[0]["serial"].ToString();
                    s_gwId = rs.Rows[0]["handleid"].ToString();
                    s_suspentype = rs.Rows[0]["suspend_type"].ToString();
                    s_memo = rs.Rows[0]["memo"].ToString();
                }
                if (!string.IsNullOrEmpty(s_gwId))
                {
                    //获得当前岗位的名称
                    sql = string.Format("select step from st_work_item where wiid='{0}' ",s_gwId);
                    s_gwname = SysParams.OAConnection().GetValue(sql);
                }

                sql = string.Format("insert into st_suspend(iid,start_date,userid,description,stpname,xt_suspend_id,suspend_type) values('{0}',sysdate ,'{1}','{2}','{3}','{4}','{5}')",strIID,s_userid,s_memo,s_gwname,s_id,s_suspentype);
            }
            else if (s_type.Equals("jcgq"))//审批人取消挂起
            {
                sql = string.Format("update xt_suspend_apply set checkuserid='{0}',delete_time=sysdate ,ischeckup='2' where id='{1}'",s_userid,s_id);
                SysParams.OAConnection().RunSql(sql);

                //修改原因:防止取消挂起后删除st_suspend表中数据，使得案件办理时间增加
                sql = string.Format("update st_suspend set end_date=sysdate ,end_userid='{0}' where xt_suspend_id='{1}' ", s_userid, s_id);

            }

            SysParams.OAConnection().RunSql(sql);
        }


        /// <summary>
        /// 添加挂起信息
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <param name="strUserId">用户id</param>
        /// <param name="strWorkFlowName">流程名</param>
        /// <param name="strBgIid">变更的办件iid</param>
        public void AddSuspend(string strIID,string strUserId,string strWorkFlowName,string strBgIid)
        {
            string strDescription = string.Empty;
            if (!string.IsNullOrEmpty(strWorkFlowName) && !string.IsNullOrEmpty(strBgIid))
            {
                strDescription = strWorkFlowName+ ":" + strBgIid;
            }

            string strSql = string.Format("insert into st_suspend(iid,start_date,userid,description) values('{0}',sysdate ,'{1}','{2}')", strIID, strUserId,strDescription);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 取消挂起
        /// </summary>
        /// <param name="strUserId">用户登录id</param>
        /// <param name="strIID">办件编号</param>
        public void CancelSuspend(string strUserId,string strIID)
        {
            string strSql = string.Format("update st_suspend set end_date=sysdate ,end_userid='{0}' where iid='{1}' ", strUserId, strIID);
            SysParams.OAConnection().RunSql(strSql);
        }
    }
}
